package milkproduction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import milkproduction.to.ContainerTO;
import milkproduction.to.MilkTO;
import milkproduction.to.RowMilkTO;
import miracom.one.MilkProduction.util.DatabaseUtility;

public class ProductionProcessDAO {
	private DatabaseUtility dbUtil;
	public ProductionProcessDAO(){
		dbUtil = DatabaseUtility.getInstance();
	}
	//원유 가져 오는 쿼리문
	public RowMilkTO selectRowMilk() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		RowMilkTO rmto = null;
		String sql = "select row_milk_no,capacity from row_milk where TO_CHAR(STOCKED_DATE,'YYYYMMDD')=TO_CHAR(SYSDATE,'YYYYMMDD')";
		try{
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				rmto = new RowMilkTO(rset.getInt(1),"",rset.getDouble(2),"");
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return rmto;
	}
	//살균 처리된(우유) 보관 테이블 시퀀스 No 가져 오기
	public int selectMilkSqe() throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "select milk_no_seq.nextval from dual";
		int sqe =0;
		try{
			conn=dbUtil.getDataSource().getConnection();
			pstmt=conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
			sqe = rset.getInt(1);
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return sqe;
	}
	//살균 처리된(우유) 보관 창고에 넣기
	public void insertMilk(int milkNo,int rowMilkNo,int capacity) throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		String sql = "insert into milk values(?,?,?,?,sysdate)";
		try{
			conn=dbUtil.getDataSource().getConnection();
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,milkNo);
			pstmt.setInt(2, rowMilkNo);
			pstmt.setInt(3, capacity);
			pstmt.setInt(4, 1);
			pstmt.executeUpdate();
		}finally{
			dbUtil.close(pstmt, conn);
		}
	}

	// 살균 우유 가져오기
	public MilkTO selectMilk() throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		MilkTO mto = null;
		String sql = "SELECT MILK_NO, ROW_MILK_NO, CAPACITY, DISPOSAL, STERILIZE_FINISH_DATE FROM MILK WHERE MIlK_NO=(SELECT MIN(MIlK_NO) FROM MILK WHERE DISPOSAL=1)";
		try{
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				mto = new MilkTO(rset.getInt(1), rset.getInt(2) , rset.getInt(3), rset.getInt(4), rset.getString(5));
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return mto;
	}
	
	public void disposalChangeMilk() throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		String sql = "UPDATE MILK SET DISPOSAL=0 WHERE MIlK_NO=(SELECT MIN(MIlK_NO) FROM MILK WHERE DISPOSAL=1)";
		try{
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		}finally{
			dbUtil.close(pstmt, conn);
		}
	}
	
	public ContainerTO selectContainer() throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		ContainerTO cto = null;
		String sql = "SELECT CONTAINER_NO, STOCKED_DATE, QUANTITY, SUPPLIER FROM CONTAINER WHERE TO_CHAR(STOCKED_DATE,'YYYYMMDD')=TO_CHAR(SYSDATE,'YYYYMMDD')";
		try{
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				cto = new ContainerTO(rset.getInt(1), rset.getString(2) , rset.getInt(3), rset.getString(4));
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return cto;
	}
	//살균 처리된(우유) 보관 테이블 시퀀스 No 가져 오기
		public int selectFinishedProductSqe() throws SQLException{
			Connection conn=null;
			PreparedStatement pstmt = null;
			ResultSet rset = null;
			String sql = "select finished_product_seq.nextval from dual";
			int sqe =0;
			try{
				conn=dbUtil.getDataSource().getConnection();
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				if(rset.next()){
				sqe = rset.getInt(1);
				}
			}finally{
				dbUtil.close(rset, pstmt, conn);
			}
			return sqe;
		}
	//완제품 정보 디비 저장
	public void insertFinishMilk(int finishedProductNo,int quantity,int rowMilkNo,int containerNo) throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt = null;
		String sql = "insert into finished_product(finished_product_no,quantity,product_day,row_milk_no,container_no) values(?,?,sysdate,?,?)";
		
	
		try{
			conn=dbUtil.getDataSource().getConnection();
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,finishedProductNo);
			pstmt.setInt(2, quantity);
			pstmt.setInt(3, rowMilkNo);
			pstmt.setInt(4, containerNo);
			pstmt.executeUpdate();
		}finally{
			dbUtil.close(pstmt, conn);
		}
	}
}
